import requests
import datetime
import pandas as pd
import pymysql
from sklearn.preprocessing import MinMaxScaler
from joblib import dump

class WeatherUtils(object):
    def __init__(self):
        self.date = [
            '2024-07-01',
            '2024-08-01',
            '2024-09-01',
            '2024-10-01',
            '2024-11-01',
            '2024-12-01',
        ]
        self.url = 'http://v1.yiketianqi.com/api'
    
    def get_data(self):
        """"
        获取天气数据
        """
        data_list = []
        for d in self.date:
            conf = {
                "appid":"76313245", #替换成自己的appid，老师的88249599
                "appsecret":"tcgOJe2l",#老师的BA7zIjrm
                "version": "history",
                "year": d[:4],
                "month":d[5:7],
                "city":"南昌",
            }
            # res = requests.get(self.url+'?',params=conf)
            res = requests.get(self.url+'?',params=conf)
            res_data = res.json()
            print(res_data)
        for i in res_data['data']:
            data_list.append({
            'date': datetime.datetime.strptime(i['ymd'],'%Y-%m-%d'),
            'bWendu': i['bWendu'],
            'yWendu': i['yWendu'],
            'tianqi': i['tianqi'],
            'fengli': i['fengli'],
            })
        df = pd.DataFrame(data_list)
        df.to_csv('./7/weather.csv')
class MysqlUtils(object):
    def __init__(self):
        self.weather_data = pd.read_csv('./7/weather.csv')
        self.conn = pymysql.connect(
            host='localhost',
            user="root",
            passwd="MYSQL123",
            db="scenic",
            port=3306,
            charset="utf8"
        )
    def is_holiday(self,date):
        '''
        是否节假日判断
        '''
        if date in ['2024-09-03','2024-10-01','2024-10-02','2024-10-03','2024-10-04',
                    '2024-10-05','2024-10-06','2024-10-07','2025-01-01','2025-01-02','2025-01-03']:
            return 1
        return 0
    def get_scenic_data(self):
        """"
        获取数据
        SELECT DATE(g.create_time) as date ,HOUR(g.create_time) as hour,
        count(*) as count FROM order_user_gate_rel g
        WHERE DATE(g.create_time) < '2025-01-01'
        GROUP BY date,hour
        2.
        SELECT DATE(g.create_time) as date ,
        count(*) as count FROM order_user_gate_rel g
        WHERE DATE(g.create_time) < '2025-01-01'
        GROUP BY  date 
        
        """
        cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = '''
        SELECT DATE(g.create_time) as date ,HOUR(g.create_time) as hour,
        count(*) as count FROM order_user_gate_rel g
        WHERE DATE(g.create_time) < '2025-01-01'
        GROUP BY date,hour
        '''
        cursor.execute(sql)
        ret = cursor.fetchall()
        df = pd.DataFrame(ret)
        # print(df.head)
        #格式转换
        date_range = pd.date_range(start='2024-07-01',end='2024-12-31',freq='D')
        hours = range(6, 24)
        full_index = pd.MultiIndex.from_product([date_range,hours],names=['date','hour'])
        # print(full_index)
        df_full = df.set_index(['date','hour']).reindex(full_index,fill_value=0).reset_index()
        df = pd.DataFrame(ret)
        #合并天气数据
        self.weather_data['date'] = pd.to_datetime(self.weather_data['date'])
        df['date'] = pd.to_datetime(df['date'])
        df_pivot = pd.merge(self.weather_data,df,on='date')
        print(df_pivot)
        df_pivot.set_index('date',inplace=True)
        df_pivot['bWendu'] = df_pivot['bWendu'].str.replace('°','').astype(int)
        df_pivot['yWendu'] = df_pivot['yWendu'].str.replace('°','').astype(int)
        df_pivot['dow'] = df_pivot.index.dayofweek #星期几
        df_pivot['month'] = df_pivot.index.month     #月份
        
        #print(df.head)
        df_pivot['is_holiday'] = df_pivot.index.map(self.is_holiday)
        
        #对星期几和月份进行独热编码
        df_pivot = pd.get_dummies(df_pivot,columns=['dow','month','tianqi','fengli'],dtype=int)
        # df_pivot = pd.get_dummies(df_pivot,columns=['dow','month'],dtype=int)
        
        #归一化入园数
        
        scaler = MinMaxScaler()
        features = df_pivot[['count']]
        df_pivot['count'] = scaler.fit_transform(features)
        #归一化天气
        weather_features = df_pivot[['bWendu','yWendu']]
        df_pivot[['bWendu','yWendu']] = scaler.fit_transform(weather_features)
        # print(df_pivot)
        dump(scaler,'scaler.joblib')
        dump(weather_features,'weather_features.joblib')
        
        
        df_pivot.to_csv('7/scenic_data.csv',index=False)
        
              
if __name__ == '__main__':
    wu =WeatherUtils()
    wu.get_data()
    mu = MysqlUtils()
    mu.get_scenic_data()